<?php

declare(strict_types=1);

namespace App\CustHub\Mapper;

use App\CustHub\Model\ChAddFriend;
use Hyperf\DbConnection\Db;
use Mine\Abstracts\AbstractMapper;
use Mine\Exception\NormalStatusException;

class ActiveAddFriendMapper extends AbstractMapper
{
    public function assignModel()
    {
        $this->model = ChAddFriend::class;
    }

    public function recordList($params)
    {
        $uid = $params['uid'];
        $group_id = $params['group_id'];

        $query = Db::table('ch_add_friend as a')
            ->selectRaw('a.id, a.mobile, a.status, a.wechat_status, a.pass_time, a.add_number, a.add_time, a.add_robot_id, a.content, a.corp_tags, a.smart_tags, a.created_at, a.updated_at, 
                     b.avatar as contact_avatar, b.nickname as contact_nickname, 
                     c.avatar as robot_avatar, c.nickname as robot_nickname,c.corp_name')
            ->leftJoin('ch_contact as b', 'a.contact_id', '=', 'b.id')
            ->leftJoin('ch_add_friend_robot as afr', 'a.add_robot_id', '=', 'afr.id')
            ->leftJoin('ch_robot as c', 'afr.robot_id', '=', 'c.id')
            ->where('a.uid', $uid)
            ->where('a.group_id', $group_id)
            // mobile (精准匹配)
            ->when(!empty($params['mobile']), function ($query) use ($params) {
                $query->where('a.mobile', $params['mobile']);
            })
            // add_robot_id (精准匹配，匹配的是 ch_add_friend_robot 表的 id)
            ->when(!empty($params['add_robot_id']), function ($query) use ($params) {
                $query->where('a.add_robot_id', $params['add_robot_id']);
            })
            // status (精准匹配)
            ->when(isset($params['status']), function ($query) use ($params) {
                $query->where('a.status', $params['status']);
            })
            // wechat_status (精准匹配)
            ->when(isset($params['wechat_status']), function ($query) use ($params) {
                $query->where('a.wechat_status', $params['wechat_status']);
            })
            // add_number (范围)
            ->when(isset($params['add_number']), function ($query) use ($params) {
                $range = explode(',', $params['add_number']);
                if (count($range) === 2) {
                    $query->whereBetween('a.add_number', [$range[0], $range[1]]);
                }
            })
            // created_at (范围)
            ->when(!empty($params['created_at']), function ($query) use ($params) {
                $range = explode(',', $params['created_at']);
                if (count($range) === 2) {
                    $query->whereBetween('a.created_at', [$range[0], $range[1]]);
                }
            })
            // pass_time (范围)
            ->when(!empty($params['pass_time']), function ($query) use ($params) {
                $range = explode(',', $params['pass_time']);
                if (count($range) === 2) {
                    $query->whereBetween('a.pass_time', [$range[0], $range[1]]);
                }
            })

            // add_time (范围)
            ->when(!empty($params['add_time']), function ($query) use ($params) {
                $range = explode(',', $params['add_time']);
                if (count($range) === 2) {
                    $query->whereBetween('a.add_time', [$range[0], $range[1]]);
                }
            })
            ->orderBy('a.created_at', 'desc');
        return $query;
    }

    public function dailyData($params)
    {
        $uid = $params['uid'];
        $addRobotId = $params['add_robot_id'] ?? null;
        // 查询最早日期（带机器人筛选）
        $firstDateQuery = $this->model::where('uid', $uid);

        if ($addRobotId !== null) {
            $firstDateQuery->where('add_robot_id', $addRobotId);
        }

        $firstDate = $firstDateQuery->min(Db::raw('DATE(add_time)'));

        if (!$firstDate) {
            return Db::table('ch_add_friend')
                ->select(
                    Db::raw('NULL as id'),
                    Db::raw('NULL as date'),
                    Db::raw('0 as total_greetings'),
                    Db::raw('0 as passed_friends'),
                    Db::raw('0 as pass_rate'),
                    Db::raw('0 as not_found_wechat')
                )
                ->whereRaw('1 = 0'); // 确保返回空结果集
        }
        $endDate = date('Y-m-d');

        // **构建日期递归查询**
        $datesSubQuery = Db::table(Db::raw("(
        WITH RECURSIVE dates AS (
            SELECT DATE('$firstDate') AS date
            UNION ALL
            SELECT DATE_ADD(date, INTERVAL 1 DAY)
            FROM dates
            WHERE date < '$endDate'
        )
        SELECT date FROM dates
    ) AS dates"));

        // **主查询**
        $query = $datesSubQuery
            ->leftJoin('ch_add_friend as f', function ($join) use ($uid, $addRobotId) {
                $join->on(Db::raw('DATE(f.add_time)'), '=', 'dates.date')
                    ->where('f.uid', $uid);

                if ($addRobotId !== null) {
                    $join->where('f.add_robot_id', $addRobotId);
                }
            })
            ->select(
                Db::raw('ROW_NUMBER() OVER (ORDER BY dates.date DESC) AS id'),
                'dates.date',
                Db::raw('IFNULL(COUNT(f.id), 0) AS total_greetings'),
                Db::raw('IFNULL(SUM(f.wechat_status = 2), 0) AS passed_friends'),
                Db::raw('IFNULL(ROUND(IFNULL(SUM(f.wechat_status = 2), 0) / NULLIF(IFNULL(COUNT(f.id), 0), 0) * 100, 2), 0) AS pass_rate'),
                Db::raw('IFNULL(SUM(f.wechat_status = 0), 0) AS not_found_wechat')
            )
            ->groupBy('dates.date')
            ->orderBy('dates.date', 'desc');
        return $query;
    }
}